#!/bin/sh
#抽取日表数据到ads_trip_engine_data_statistics_pdi表中
CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
else
  hive_db=${hive_db}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

sql_suffix=$(cat <<EOF
  insert overwrite table ${hive_db}.ads_trip_engine_data_statistics_pdi partition (day=${part_time})
  select
    vin,
    tid,
    car_type,
    engine_type,
    trip_id,
    emission_standard,
    route_start_time,
    route_end_time,
    if(exist_engine_data_miss > 0, 1, 0) as exist_engine_data_miss,
    engine_data_miss_terms,
    if(speed_miss_duration / route_duration > 0.34, 1, 0) as speed_miss,
    if(barometric_pressure_miss_duration / route_duration > 0.34, 1, 0) as barometric_pressure_miss,
    if(output_torque_miss_duration / route_duration > 0.34, 1, 0) as output_torque_miss,
    if(friction_torque_miss_duration / route_duration > 0.34, 1, 0) as friction_torque_miss,
    if(rotation_miss_duration / route_duration > 0.34, 1, 0) as rotation_miss,
    if(fuel_consumption_miss_duration / route_duration > 0.34, 1, 0) as fuel_consumption_miss,
    if(scr_up_nox_miss_duration / route_duration > 0.34, 1, 0) as scr_up_nox_miss,
    if(scr_down_nox_miss_duration / route_duration > 0.34, 1, 0) as scr_down_nox_miss,
    if(reagent_allowance_miss_duration / route_duration > 0.34, 1, 0) as reagent_allowance_miss,
    if(air_inflow_miss_duration / route_duration > 0.34, 1, 0) as air_inflow_miss,
    if(scr_in_miss_duration / route_duration > 0.34, 1, 0) as scr_in_miss,
    if(scr_out_miss_duration / route_duration > 0.34, 1, 0) as scr_out_miss,
    if(dropout_voltage_miss_duration / route_duration > 0.34, 1, 0) as dropout_voltage_miss,
    if(coolant_temperature_miss_duration / route_duration > 0.34, 1, 0) as coolant_temperature_miss,
    if(tank_level_miss_duration / route_duration > 0.34, 1, 0) as tank_level_miss,
    if(positioning_state_miss_duration / route_duration > 0.34, 1, 0) as positioning_state_miss,
    if(longitude_miss_duration / route_duration > 0.34, 1, 0) as longitude_miss,
    if(latitude_miss_duration / route_duration > 0.34, 1, 0) as latitude_miss,
    if(mileage_miss_duration / route_duration > 0.34, 1, 0) as mileage_miss,
    if(ternary_down_nox_output_miss_duration / route_duration > 0.34, 1, 0) as ternary_down_nox_output_miss,
    if(ternary_up_o2_output_miss_duration / route_duration > 0.34, 1, 0) as ternary_up_o2_output_miss,
    if(ternary_down_o2_output_miss_duration / route_duration > 0.34, 1, 0) as ternary_down_o2_output_miss,
    if(exist_engine_data_abnormal > 0, 1, 0) as exist_engine_data_abnormal,
    engine_data_abnormal_terms,
    if(speed_abnm_duration / route_duration > 0.34, 1, 0) as speed_abnormal,
    if(barometric_pressure_abnm_duration / route_duration > 0.34, 1, 0) as barometric_pressure_abnormal,
    if(output_torque_abnm_duration / route_duration > 0.34, 1, 0) as output_torque_abnormal,
    if(friction_torque_abnm_duration / route_duration > 0.34, 1, 0) as friction_torque_abnormal,
    if(rotation_abnm_duration / route_duration > 0.34, 1, 0) as rotation_abnormal,
    if(fuel_consumption_abnm_duration / route_duration > 0.34, 1, 0) as fuel_consumption_abnormal,
    if(scr_up_nox_abnm_duration / route_duration > 0.34, 1, 0) as scr_up_nox_abnormal,
    if(scr_down_nox_abnm_duration / route_duration > 0.34, 1, 0) as scr_down_nox_abnormal,
    if(reagent_allowance_abnm_duration / route_duration > 0.34, 1, 0) as reagent_allowance_abnormal,
    if(air_inflow_abnm_duration / route_duration > 0.34, 1, 0) as air_inflow_abnormal,
    if(scr_in_abnm_duration / route_duration > 0.34, 1, 0) as scr_in_abnormal,
    if(scr_out_abnm_duration / route_duration > 0.34, 1, 0) as scr_out_abnormal,
    if(dropout_voltage_abnm_duration / route_duration > 0.34, 1, 0) as dropout_voltage_abnormal,
    if(coolant_temperature_abnm_duration / route_duration > 0.34, 1, 0) as coolant_temperature_abnormal,
    if(tank_level_abnm_duration / route_duration > 0.34, 1, 0) as tank_level_abnormal,
    if(ternary_down_nox_output_abnm_duration / route_duration > 0.34, 1, 0) as ternary_down_nox_output_abnormal,
    if(ternary_up_o2_output_abnm_duration / route_duration > 0.34, 1, 0) as ternary_up_o2_output_abnormal,
    if(ternary_down_o2_output_abnm_duration / route_duration > 0.34, 1, 0) as ternary_down_o2_output_abnormal,
    if(cast(speed_total / (engine_data_points - speed_miss_points) as int) is NULL, 0, cast(speed_total / (engine_data_points - speed_miss_points) as int)) as speed_avg,
    if(cast(barometric_pressure_total / (engine_data_points - barometric_pressure_miss_points) as int) is NULL, 0, cast(barometric_pressure_total / (engine_data_points - barometric_pressure_miss_points) as int)) as barometric_pressure_avg,
    if(cast(output_torque_total / (engine_data_points - output_torque_miss_points) as int) is NULL, 0, cast(output_torque_total / (engine_data_points - output_torque_miss_points) as int)) as output_torque_avg,
    if(cast(friction_torque_total / (engine_data_points - friction_torque_miss_points) as int) is NULL, 0, cast(friction_torque_total / (engine_data_points - friction_torque_miss_points) as int)) as friction_torque_avg,
    if(cast(rotation_total / (engine_data_points - rotation_miss_points) as int) is NULL, 0, cast(rotation_total / (engine_data_points - rotation_miss_points) as int)) as rotation_avg,
    if(cast(fuel_consumption_total / (engine_data_points - fuel_consumption_miss_points) as int) is NULL, 0, cast(fuel_consumption_total / (engine_data_points - fuel_consumption_miss_points) as int)) as fuel_consumption_avg,
    if(cast(scr_up_nox_total / (engine_data_points - scr_up_nox_miss_points) as int) is NULL, 0, cast(scr_up_nox_total / (engine_data_points - scr_up_nox_miss_points) as int)) as scr_up_nox_avg,
    if(cast(scr_down_nox_total / (engine_data_points - scr_down_nox_miss_points) as int) is NULL, 0, cast(scr_down_nox_total / (engine_data_points - scr_down_nox_miss_points) as int)) as scr_down_nox_avg,
    if(cast(reagent_allowance_total / (engine_data_points - reagent_allowance_miss_points) as int) is NULL, 0, cast(reagent_allowance_total / (engine_data_points - reagent_allowance_miss_points) as int)) as reagent_allowance_avg,
    if(cast(air_inflow_total / (engine_data_points - air_inflow_miss_points) as int) is NULL, 0, cast(air_inflow_total / (engine_data_points - air_inflow_miss_points) as int)) as air_inflow_avg,
    if(cast(scr_in_total / (engine_data_points - scr_in_miss_points) as int) is NULL, 0, cast(scr_in_total / (engine_data_points - scr_in_miss_points) as int)) as scr_in_avg,
    if(cast(scr_out_total / (engine_data_points - scr_out_miss_points) as int) is NULL, 0, cast(scr_out_total / (engine_data_points - scr_out_miss_points) as int)) as scr_out_avg,
    if(cast(dropout_voltage_total / (engine_data_points - dropout_voltage_miss_points) as int) is NULL, 0, cast(dropout_voltage_total / (engine_data_points - dropout_voltage_miss_points) as int)) as dropout_voltage_avg,
    if(cast(coolant_temperature_total / (engine_data_points - coolant_temperature_miss_points) as int) is NULL, 0, cast(coolant_temperature_total / (engine_data_points - coolant_temperature_miss_points) as int)) as coolant_temperature_avg,
    if(cast(tank_level_total / (engine_data_points - tank_level_miss_points) as int) is NULL, 0, cast(tank_level_total / (engine_data_points - tank_level_miss_points) as int)) as tank_level_avg,
    if(cast(ternary_down_nox_output_total / (engine_data_points - ternary_down_nox_output_miss_points) as int) is NULL, 0, cast(ternary_down_nox_output_total / (engine_data_points - ternary_down_nox_output_miss_points) as int)) as ternary_down_nox_output_avg,
    if(cast(ternary_up_o2_output_total / (engine_data_points - ternary_up_o2_output_miss_points) as int) is NULL, 0, cast(ternary_up_o2_output_total / (engine_data_points - ternary_up_o2_output_miss_points) as int)) as ternary_up_o2_output_avg,
    if(cast(ternary_down_o2_output_total / (engine_data_points - ternary_down_o2_output_miss_points) as int) is NULL, 0, cast(ternary_down_o2_output_total / (engine_data_points - ternary_down_o2_output_miss_points) as int)) as ternary_down_o2_output_avg,
    speed_max,
    barometric_pressure_max,
    output_torque_max,
    friction_torque_max,
    rotation_max,
    fuel_consumption_max,
    scr_up_nox_max,
    scr_down_nox_max,
    reagent_allowance_max,
    air_inflow_max,
    scr_in_max,
    scr_out_max,
    dropout_voltage_max,
    coolant_temperature_max,
    tank_level_max,
    ternary_down_nox_output_max,
    ternary_up_o2_output_max,
    ternary_down_o2_output_max,
    speed_min,
    barometric_pressure_min,
    output_torque_min,
    friction_torque_min,
    rotation_min,
    fuel_consumption_min,
    scr_up_nox_min,
    scr_down_nox_min,
    reagent_allowance_min,
    air_inflow_min,
    scr_in_min,
    scr_out_min,
    dropout_voltage_min,
    coolant_temperature_min,
    tank_level_min,
    ternary_down_nox_output_min,
    ternary_up_o2_output_min,
    ternary_down_o2_output_min,
    ternary_temperature_output_min,
    ternary_temperature_output_max,
    if(cast(ternary_temperature_output_total / (engine_data_points - ternary_temperature_output_miss_points) as int) is NULL, 0, cast(ternary_temperature_output_total / (engine_data_points - ternary_temperature_output_miss_points) as int)) as ternary_temperature_output_avg,
    if(ternary_temperature_output_miss_duration / route_duration > 0.34, 1, 0) as ternary_temperature_output_miss,
    if(ternary_temperature_output_abnm_duration / route_duration > 0.34, 1, 0) as ternary_temperature_output_abnormal
  from ${hive_db}.dws_trip_engine_data_pdi
  where day = ${part_time};
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  "${CUR_DIR}"/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入ads_trip_engine_data_statistics_pdi的SQL为：${sql}"

#执行语句·
hive --hiveconf "tez.queue.name=${queue_name}" -e "${sql}"
if [ $? -ne 0 ];then
  echo "导入ads_trip_engine_data_statistics_pdi失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入ads_trip_engine_data_statistics_pdi成功，耗时$((`date +%s`-START_TIME))秒..."
fi